"""
Case Type   : GUC
Case Name   : 修改track_functions为on，观察预期结果；
Create At   : 2020/11/23
Owner       : opentestcase018
Description :
    1、查询track_functions默认值;查询PG_STAT_USER_FUNCTIONS视图为空
    show track_functions;
    select count(*) from PG_STAT_USER_FUNCTIONS;
    2、修改track_functions为all，重启使其生效，并校验其预期结果
    gs_guc set -D {cluster/dn1} -c "track_functions=all"
    gs_om -t stop && gs_om -t start
    show track_functions;
    3、重启后做DML 1000+，等待1min查询PG_STAT_USER_FUNCTIONS视图
    4、清理环境
Expect      :
    1、显示默认值none;PG_STAT_USER_FUNCTIONS视图为空
    2、参数修改成功，校验修改后系统参数值为all
    3、DML无报错，PG_STAT_USER_FUNCTIONS视图不为空
    4、清理成功
History     :
    Modified: 2021/07/02  modified by opentestcase018: 优化重启后的断言
    Modified: 2022/05/26  modified by opentestcase013: 增加等待时间、优化格式
"""

import sys
import unittest
import os

sys.path.append(sys.path[0] + "/../")
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH

logger = Logger()
commonsh = CommonSH('PrimaryDbUser')


class Guctestcase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.Constant = Constant()
        self.func_name = 'func_statistics_0007'

    def test_guc(self):
        logger.info('--step1.1:查询track_functions；expect:默认值none--')
        res = commonsh.execut_db_sql('show track_functions;')
        logger.info(res)
        self.assertEqual("none", res.split("\n")[-2].strip())

        logger.info('--step1.2:查询PG_STAT_USER_FUNCTIONS视图；expect:为空--')
        sql_cmd = 'select count(*) from PG_STAT_USER_FUNCTIONS;'
        res = commonsh.execut_db_sql(sql_cmd)
        logger.info(res)
        self.assertEqual(int(res.split("\n")[-2].strip()), 0)

        logger.info('--step2:修改track_functions为all，'
                    '重启使其生效；expect:设置成功--')
        res = commonsh.execute_gsguc('set', self.Constant.GSGUC_SUCCESS_MSG,
                                     'track_functions=all')
        self.assertTrue(res)
        commonsh.restart_db_cluster()
        status = commonsh.get_db_cluster_status()
        self.assertTrue("Normal" in status or "Degraded" in status)
        res = commonsh.execut_db_sql('''show track_functions;''')
        logger.info(res)
        self.assertEqual("all", res.split("\n")[-2].strip())

        logger.info('--step3.1:执行DML；expect:执行成功')
        sql_cmd = f'''
        drop function if exists {self.func_name}(c int);
        create or replace function {self.func_name}(c int)return number as
        b int := c;
        begin
            for i in 1..c loop
                b:= b - 1;
            end loop;
            return b;
        end;
        select {self.func_name}(5);
        '''
        res = commonsh.execut_db_sql(sql_cmd)
        logger.info(res)
        self.assertNotIn(self.Constant.SQL_WRONG_MSG[1], res)

        logger.info('--step3.2:等待1min,查询PG_STAT_USER_FUNCTIONS视图；'
                    'expect:不为空')
        res = commonsh.execut_db_sql('''
        select pg_sleep(60);select count(*) from PG_STAT_USER_FUNCTIONS;
        ''')
        logger.info(res)
        self.assertGreater(int(res.split("\n")[-2].strip()), 0)

    def tearDown(self):
        logger.info('--step4:清理环境；expect:成功')
        logger.info('--step4.1:删除function；expect:成功')
        drop_func = commonsh.execut_db_sql(f'drop function {self.func_name};')
        logger.info(drop_func)

        logger.info('--step4.2:恢复默认值；expect:成功')
        res = commonsh.execut_db_sql('show track_functions;')
        if "none" not in res:
            commonsh.execute_gsguc('set', self.Constant.GSGUC_SUCCESS_MSG,
                                   'track_functions=none')
            commonsh.restart_db_cluster()
        status = commonsh.get_db_cluster_status()
        logger.info(status)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
        self.assertIn(self.Constant.DROP_FUNCTION_SUCCESS_MSG, drop_func)
        self.assertTrue("Normal" in status or "Degraded" in status)
